package com.bbseguros.pesquisador.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.bbseguros.pesquisador.bean.GruposBean;
import com.bbseguros.pesquisador.util.ConnectDB;


/**
 * @author hmsilva
 * 
 */
public class GruposDAO {

	private static Logger logger = Logger.getLogger(GruposDAO.class);

	private Connection conn = null;
	private static GruposDAO instance = null;

	private GruposDAO() {
		try {
			conn = ConnectDB.getConnection();
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}		
	}
	
	public static GruposDAO getInstance(){
		if(instance == null){
			instance = new GruposDAO();
		}
		return instance;
	}
	

	/**
	 * @param userName
	 * @param password
	 * @return
	 */
	public List<GruposBean> selectAllGroups(){
		GruposBean group = null;
		List<GruposBean> grupos = new ArrayList<GruposBean>();
		
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("SELECT * FROM tbl_grupo;");
			
			stmt = conn.prepareStatement(buffer.toString());
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				group = new GruposBean();
				group.setId(rs.getInt("id_grupo"));
				group.setNome(rs.getString("nome"));
				group.setDescricao(rs.getString("descricao"));
				grupos.add(group);
			}
		
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return grupos;
	}

	public GruposBean selectGroupById(int idGrupo){
		GruposBean group = null;
		
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("SELECT * FROM tbl_grupo WHERE id_grupo = ?;");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setInt(1, idGrupo);
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				group = new GruposBean();
				group.setId(rs.getInt("id_grupo"));
				group.setNome(rs.getString("nome"));
				group.setDescricao(rs.getString("descricao"));
			}
		
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return group;
	}
	
	/**
	 * @param bean
	 * @return
	 */
	public boolean insertGroup(GruposBean bean) {
		boolean toReturn = false;
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			int idGrupo = getGroupId();
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("INSERT INTO tbl_grupo (id_grupo, nome, descricao)");
			buffer.append("VALUES (?,?,?)");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setInt(1, idGrupo);
			stmt.setString(2, bean.getNome());
			stmt.setString(3, bean.getDescricao());			

			int ret = stmt.executeUpdate();	
			if(ret>0) {
				toReturn = true;
			}
			logger.debug("toReturn: "+toReturn);
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return toReturn;
	}
	
	public boolean updateGroup(GruposBean bean) {
		boolean toReturn = false;
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			StringBuffer buffer = new StringBuffer();			
			buffer.append("UPDATE tbl_grupo SET nome=?, descricao=?");
			buffer.append("WHERE id_grupo=?");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setString(1, bean.getNome());
			stmt.setString(2, bean.getDescricao());
			stmt.setInt(3, bean.getId());

			int ret = stmt.executeUpdate();	
			if(ret>0) {
				toReturn = true;
			}
			logger.debug("toReturn: "+toReturn);
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return toReturn;
	}
	
	public boolean deletedGroupById(int idGrupo){
		boolean toReturn = false;
		
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("DELETE FROM tbl_grupo WHERE id_grupo = ?;");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setInt(1, idGrupo);
			
			int ret = stmt.executeUpdate();	
			if(ret>0) {
				toReturn = true;
			}
			logger.debug("toReturn: "+toReturn);
		
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return toReturn;
	}	
	
	public int getGroupId(){
		if (conn == null) {
			return 0;
		}
		Statement stmt = null;
		int returnId = 0;

		try {
			returnId = ConnectDB.getSequenceMySql(conn, "seq_grupo");

		} catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
		}  finally {
			ConnectDB.cleanUp(stmt);
		}
		return returnId;
	}
}
